﻿using FySystem.Data.OrmA.Attributes;
using FySystem.Data.OrmA.Ext;
using FySystem.Data.OrmA.Reflection;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Text.Unicode;

namespace FySystem.Data.OrmA.Formatter
{
    internal class BaseFormatter
    {
        /// <summary>
        /// 将带有参数“?”的SQL转换为标准的SQL+数据库参数
        /// </summary>
        /// <param name="sqlFormatter"></param>
        /// <param name="dbProviderFactory"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        internal static FormatResult CreateSQLAndParameters(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, string sql, params object[] parameters)
        {
            FormatResult res = new FormatResult();
            try
            {
                if (parameters.IsNullOrEmpty())
                {
                    res.Sql = sql;
                    res.Parameters = null;
                }
                else
                {
                    int paramerterCount = 0;
                    res.Sql = ReplaceInternalParameters(sqlFormatter, sql, ref paramerterCount);
                    if (paramerterCount != parameters.Length)
                        throw new Exception("传入参数个数不匹配~");

                    res.Parameters = CreateSQLParameters(sqlFormatter, dbProviderFactory, parameters);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 创建Get语句
        /// </summary>
        /// <param name="reflectionInfo"></param>
        internal static FormatResult CreateGetSQL(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, ReflectionInfo reflectionInfo, object value)
        {
            FormatResult res = null;
            try
            {
                if (value == null)
                    throw new Exception("传入主键值为空~");
                if (reflectionInfo.Table == null)
                    throw new Exception("不是Model类，不支持Get方法~");
                if (reflectionInfo.PrimaryKey == null)
                    throw new Exception("没有主键，不支持Get方法~");

                res = new FormatResult();

                //先创建Select部分SQL
                string selectSQL = CreateSelectPartSQL(sqlFormatter, reflectionInfo, true);

                //再创建Where部分SQL
                DbParameter pk = CreateSQLParameter(sqlFormatter, dbProviderFactory, reflectionInfo.PrimaryKey.Column.DbFieldType, 0, value).Item1;
                res.Sql = $"SELECT {selectSQL} FROM {GetDbFullName(reflectionInfo.Table)} WHERE {sqlFormatter.FormatFields(reflectionInfo.PrimaryKey.Column.MappingColumn)} = {pk.ParameterName}";
                res.Parameters = new DbParameter[] { pk };
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 创建新增数据SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlFormatter"></param>
        /// <param name="dbProviderFactory"></param>
        /// <param name="reflectionInfo"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        internal static FormatResult CreateSaveSQL<T>(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, 
            ReflectionInfo reflectionInfo, T model, string midSQL, ref object pkValue, bool isReturn)
        {
            FormatResult res = null;
            try
            {
                res = new FormatResult();

                bool ignorePk = false;
                FyPrimaryKeyAttribute primaryAttr = (FyPrimaryKeyAttribute)reflectionInfo.PrimaryKey.Column;
                if (primaryAttr.PrimaryKeyType == PrimaryKeyTypes.AutoIncremental)
                    ignorePk = true;

                StringBuilder sql = new StringBuilder();
                string dbFullname = GetDbFullName(reflectionInfo.Table);

                //先创建字段部分
                string insertSQL = CreateSelectPartSQL(sqlFormatter, reflectionInfo, true, false, ignorePk);

                //如果是Oracle数据库，并且用的是Sequence，且不用返回主键，则ID部分是字符串：****.NEXTVAL
                string idStr = string.Empty;
                if(sqlFormatter.DbType== DbTypes.Oracle && isReturn == false)
                {
                    idStr = $"{primaryAttr.Sequence}.NEXTVAL,";
                    ignorePk = true;
                }
                else if(sqlFormatter.DbType== DbTypes.Sqlserver && primaryAttr.PrimaryKeyType== PrimaryKeyTypes.GUIDN)
                {
                    idStr = $"NEWID(),";
                    ignorePk = true;
                }

                string schema = string.Empty;
                if (!string.IsNullOrWhiteSpace(reflectionInfo.Table.Schema))
                    schema = $"{reflectionInfo.Table.Schema}.";

                //再创建参数部分
                FormatResult fmtValues = FormatSavePartSQL(sqlFormatter, dbProviderFactory, reflectionInfo, model, ignorePk,
                     ref pkValue, true);

                //这里有点乱，暂时只能这么写了
                if (sqlFormatter.DbType == DbTypes.Sqlserver)
                {
                    sql.Append($"INSERT INTO {schema}{reflectionInfo.Table.Table} ({insertSQL}) {midSQL} VALUES ({idStr}");
                    sql.Append($"{fmtValues.Sql})");
                }
                else
                {
                    sql.Append($"INSERT INTO {schema}{reflectionInfo.Table.Table} ({insertSQL}) VALUES ({idStr}");
                    sql.Append($"{fmtValues.Sql}) {midSQL}");
                }
                res = new FormatResult();
                res.Sql = sql.ToString();
                res.Parameters = fmtValues.Parameters;
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 创建更新SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlFormatter"></param>
        /// <param name="dbProviderFactory"></param>
        /// <param name="reflectionInfo"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        internal static FormatResult CreateUpdateSQL<T>(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, ReflectionInfo reflectionInfo, T model)
        {
            FormatResult res = new FormatResult();
            try
            {
                if (model == null)
                    throw new Exception("传入Model为空~");
                if (reflectionInfo.Table == null)
                    throw new Exception("不是Model类，不支持Update方法~");
                if (reflectionInfo.PrimaryKey == null)
                    throw new Exception("没有主键，不支持Update方法~");

                List<DbParameter> parameters = new List<DbParameter>();

                string dbFullName = GetDbFullName(reflectionInfo.Table);
                StringBuilder sql = new StringBuilder($"UPDATE {dbFullName} SET ");

                int index = 0;
                bool isFirst = true;
                Tuple<DbParameter, string> primaryKey = null;
                foreach (var item in reflectionInfo.Mapping.Values)
                {
                    object paramValue = item.PropertyInfo.GetValue(model);
                    if (item.Column.IsPrimaryKey)
                    {
                        primaryKey = CreateSQLParameter(sqlFormatter, dbProviderFactory, item.Column.DbFieldType, index, paramValue);
                        index++;
                        continue;
                    }

                    Tuple<DbParameter, string> parameter = CreateSQLParameter(sqlFormatter, dbProviderFactory, item.Column.DbFieldType, index, paramValue);

                    if (isFirst == false)
                        sql.Append(',');
                    if (parameter.Item1 == null)
                        sql.Append($"{sqlFormatter.FormatFields(item.Column.MappingColumn)} = {parameter.Item2}");
                    else
                    {
                        sql.Append($"{sqlFormatter.FormatFields(item.Column.MappingColumn)} = {parameter.Item1.ParameterName}");
                        parameters.Add(parameter.Item1);
                    }
                    index++;
                    isFirst = false;
                }
                sql.Append($" WHERE {sqlFormatter.FormatFields(reflectionInfo.PrimaryKey.Column.MappingColumn)} = {primaryKey.Item1.ParameterName}");
                parameters.Add(primaryKey.Item1);

                res.Sql = sql.ToString();
                res.Parameters = parameters.ToArray();
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 创建删除SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlFormatter"></param>
        /// <param name="dbProviderFactory"></param>
        /// <param name="reflectionInfo"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        internal static FormatResult CreateDeleteSQL<T>(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, ReflectionInfo reflectionInfo, T model)
        {
            FormatResult res = new FormatResult();
            try
            {
                if (model == null)
                    throw new Exception("传入Model为空~");
                if (reflectionInfo.Table == null)
                    throw new Exception("不是Model类，不支持Delete方法~");
                if (reflectionInfo.PrimaryKey == null)
                    throw new Exception("没有主键，不支持Delete方法~");

                object paramValue = reflectionInfo.PrimaryKey.PropertyInfo.GetValue(model);
                if (paramValue == null)
                    throw new Exception("主键不能为空~");

                Tuple<DbParameter, string> primaryKey = CreateSQLParameter(sqlFormatter, dbProviderFactory, reflectionInfo.PrimaryKey.Column.DbFieldType, 0, paramValue);
                string deletePartSQL = CreatePreDeletePartSQL(reflectionInfo);
                res.Sql = $"{deletePartSQL} WHERE {reflectionInfo.PrimaryKey.Column.MappingColumn} = {primaryKey.Item1.ParameterName}";
                res.Parameters = new DbParameter[] { primaryKey.Item1 };
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 创建分页SQL
        /// </summary>
        /// <param name="sqlFormatter"></param>
        /// <param name="dbProviderFactory"></param>
        /// <param name="reflectionInfo"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        internal static PgFormatResult CreatePageQuerySQL(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, ReflectionInfo reflectionInfo, 
            string sql, int pageNumber, int perPage, string orderBy, object[] parameters)
        {
            PgFormatResult res = new PgFormatResult();
            try
            {
                //生成查询总数据条数的SQL
                FormatResult fmtCountSQL = CreateSQLAndParameters(sqlFormatter, dbProviderFactory, sql, parameters);
                res.CountSql = $"select count(1) from ({fmtCountSQL.Sql}) fytt";
                res.Parameters = fmtCountSQL.Parameters;

                //生成查询记录的SQL
                res.Sql = sqlFormatter.ToPgQuerySQL(sql, pageNumber, perPage, orderBy);
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        internal static string GetDbFullName(FyTableAttribute table)
        {
            return !string.IsNullOrWhiteSpace(table.Schema) ? $"{table.Schema}.{table.Table}" : table.Table;
        }

        /// <summary>
        /// 根据实体创建Select部分的SQL
        /// </summary>
        /// <param name="sqlFormatter"></param>
        /// <param name="reflectionInfo"></param>
        /// <param name="isOnlyDbColumns">是否只创建带有FyColumn特性的属性</param>
        /// <param name="isAll">如果为true，则生成*，优先级大于isOnlyDbColumns</param>
        /// <returns></returns>
        internal static string CreateSelectPartSQL(ISqlFormatter sqlFormatter, ReflectionInfo reflectionInfo, bool isOnlyDbColumns, bool isAll = false, bool ignorePk = false)
        {
            if (isAll)
                return "*";

            StringBuilder sql = new StringBuilder("");
            try
            {
                if (reflectionInfo.Mapping == null)
                    throw new Exception("Model未设置属性字段~");
                if (reflectionInfo.Mapping.Count <= 0)
                    throw new Exception("Model未设置属性字段~");

                bool isFirst = true;
                foreach (var column in reflectionInfo.Mapping.Values)
                {
                    bool needToAdd = true;
                    if ((isOnlyDbColumns == true && column.IsDbColumn == false) || (ignorePk && column.Column.IsPrimaryKey))
                        needToAdd = false;

                    if (needToAdd)
                    {
                        if (isFirst == false)
                            sql.Append(',');
                        sql.Append(sqlFormatter.FormatFields(column.Column.MappingColumn));
                        isFirst = false;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return sql.ToString();
        }



        /// <summary>
        /// 创建SQL参数
        /// </summary>
        /// <param name="sqlFormatter"></param>
        /// <param name="dbProviderFactory"></param>
        /// <param name="value"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        internal static Tuple<DbParameter,string> CreateSQLParameter(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, DbFieldTypes dbFieldType, int index, object value)
        {
            try
            {
                DbParameter parameter = null;
                string valueString = string.Empty;

                //对于一些特殊的参数，特殊处理，目前只有JSON是特殊的
                if (value == null)
                {
                    parameter = dbProviderFactory.CreateParameter();
                    parameter.ParameterName = $"{sqlFormatter.ParameterMark}P{index}";
                    parameter.Value = DBNull.Value;
                }
                else
                {
                    switch (dbFieldType)
                    {
                        case DbFieldTypes.Default:
                            parameter = dbProviderFactory.CreateParameter();
                            parameter.ParameterName = $"{sqlFormatter.ParameterMark}P{index}";
                            parameter.Value = value;
                            break;
                        case DbFieldTypes.Json:
                            valueString = sqlFormatter.ConvertDbJsonValue(value);
                            break;
                        default:
                            throw new Exception("不支持的参数类型~");
                    }
                }
                
                return new Tuple<DbParameter, string>(parameter, valueString);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 创建SQL参数
        /// </summary>
        /// <param name="dbProviderFactory"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        internal static DbParameter[] CreateSQLParameters(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, object[] parameters)
        {
            DbParameter[] res = new DbParameter[parameters.Length];
            try
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    DbParameter parameter = dbProviderFactory.CreateParameter();
                    parameter.ParameterName = $"{sqlFormatter.ParameterMark}P{i}";
                    parameter.Value = parameters[i];
                    res[i] = parameter;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 根据实体创建SQL参数
        /// </summary>
        /// <param name="sqlFormatter"></param>
        /// <param name="reflectionInfo"></param>
        /// <param name="ignorePk"></param>
        /// <returns></returns>
        internal static FormatResult FormatSavePartSQL<T>(ISqlFormatter sqlFormatter, DbProviderFactory dbProviderFactory, ReflectionInfo reflectionInfo,
            T model, bool ignorePk, ref object pkValue, bool isOnlyDbColumn)
        {
            FormatResult res = new FormatResult();
            List<DbParameter> parameters = new List<DbParameter>();
            StringBuilder valueSQL = new StringBuilder();
            try
            {
                int index = 0;

                foreach(var item in reflectionInfo.Mapping.Values)
                {
                    if (isOnlyDbColumn && item.IsDbColumn == false)
                        continue;

                    Tuple<DbParameter, string> parameter = null;

                    if (item.Column.IsPrimaryKey)
                    {
                        //如果是主键
                        if (ignorePk)
                            continue;
                        else
                        {
                            FyPrimaryKeyAttribute primaryAttr = (FyPrimaryKeyAttribute)reflectionInfo.PrimaryKey.Column;
                            switch (primaryAttr.PrimaryKeyType)
                            {
                                case PrimaryKeyTypes.Custom:
                                    //主键的值直接用实体中的
                                    pkValue = item.PropertyInfo.GetValue(model);
                                    break;
                                case PrimaryKeyTypes.GUID:
                                    //主键用guid，为了提高效率，不再和之前一样转大写
                                    pkValue = Guid.NewGuid().ToString("N");
                                    break;
                                case PrimaryKeyTypes.GUIDN:
                                    pkValue = Guid.NewGuid();
                                    break;
                            }
                        }
                        parameter = CreateSQLParameter(sqlFormatter, dbProviderFactory, item.Column.DbFieldType, index, pkValue);
                    }
                    else
                    {
                        //如果不是主键
                        object paramValue = item.PropertyInfo.GetValue(model);
                        parameter = CreateSQLParameter(sqlFormatter, dbProviderFactory, item.Column.DbFieldType, index, paramValue);
                    }

                    //加入结果
                    if (parameter != null)
                    {
                        if (index > 0)
                            valueSQL.Append(',');

                        if (parameter.Item1 != null)
                        {
                            parameters.Add(parameter.Item1);
                            valueSQL.Append(parameter.Item1.ParameterName);
                        }
                        else
                        {
                            valueSQL.Append(parameter.Item2);
                        }
                    }
                    index += 1;
                }

                res.Parameters = parameters.ToArray();
                res.Sql = valueSQL.ToString();
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 创建删除语句，Where条件之前的部分
        /// </summary>
        /// <param name="reflectionInfo"></param>
        /// <returns></returns>
        internal static string CreatePreDeletePartSQL(ReflectionInfo reflectionInfo)
        {
            try
            {
                string schema = string.Empty;
                if (!string.IsNullOrWhiteSpace(reflectionInfo.Table.Schema))
                    schema = $"{reflectionInfo.Table.Schema}.";
                return $"DELETE FROM {schema}{reflectionInfo.Table.Table}";
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 将SQL中的?替换为标准参数写法:P0,:P1
        /// </summary>
        /// <param name="sourceSQL"></param>
        /// <param name="paramerterCount"></param>
        /// <returns></returns>
        internal static string ReplaceInternalParameters(ISqlFormatter sqlFormatter, string sourceSQL, ref int paramerterCount)
        {
            StringBuilder res = new StringBuilder();
            try
            {
                string[] sqlPart = sourceSQL.Split('?');
                paramerterCount = sqlPart.Length - 1;

                for (int i = 0; i < sqlPart.Length - 1; i++)
                    res.Append(sqlPart[i]).Append(sqlFormatter.ParameterMark).Append($"P{i}");
                res.Append(sqlPart[sqlPart.Length - 1]);
            }
            catch (Exception)
            {
                throw;
            }
            return res.ToString();
        }
    }
}
